{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2021年“泰迪杯”数据分析技能赛 A题 通讯产品销售和盈利能力分析"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import DataFrame,Series\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 一、背景\n",
    "进入本世纪以来，我国通讯产品得到了飞速发展，其技术先进，价格便宜，\n",
    "深受世界各国和地区尤其是非洲国家的欢迎。某通讯公司在非洲的多个国家深耕\n",
    "多年，产品与服务遍布整个非洲大陆。为了更好地了解公司的销售情况，采用产\n",
    "品的销售额和利润数据，对其盈利能力进行分析和预测，给决策人员提供分析报\n",
    "告，以便为非洲各国提供更好的产品销售策略和服务。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 二、目标\n",
    "1. 统 计产品在当地的销售数据，预测未来的销售情况。\n",
    "2. 设计 可视化数字大屏，展示产品的销售情况，分析产品的盈利能力。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 三、任务\n",
    "请根据附件中提供的数据（其中，金额计量单位：万元；合同数计量单位：\n",
    "个）， 自行选择分析工具完成以下任务 ，并撰写报告。如使用 TipDM-BI数据\n",
    "分析和可视化平台”实现，使用方式详见附录 。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 任务 1 数据分析与预测"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>城市</th>\n",
       "      <th>地区</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019</td>\n",
       "      <td>Angola</td>\n",
       "      <td>Luanda</td>\n",
       "      <td>Middle</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>421.39</td>\n",
       "      <td>-25.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019</td>\n",
       "      <td>Cameroon</td>\n",
       "      <td>Douala</td>\n",
       "      <td>Middle</td>\n",
       "      <td>Public</td>\n",
       "      <td>215.26</td>\n",
       "      <td>8.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019</td>\n",
       "      <td>Botswana</td>\n",
       "      <td>Gaborone</td>\n",
       "      <td>Southern</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>471.08</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019</td>\n",
       "      <td>Egypt</td>\n",
       "      <td>Cairo</td>\n",
       "      <td>Northern</td>\n",
       "      <td>Public</td>\n",
       "      <td>435.44</td>\n",
       "      <td>21.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019</td>\n",
       "      <td>Equatorial Guinea</td>\n",
       "      <td>Malabo</td>\n",
       "      <td>Middle</td>\n",
       "      <td>Residential</td>\n",
       "      <td>207.77</td>\n",
       "      <td>6.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>2019</td>\n",
       "      <td>Malawi</td>\n",
       "      <td>Blantyre</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>692.45</td>\n",
       "      <td>6.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260</th>\n",
       "      <td>2019</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>Kano</td>\n",
       "      <td>Western</td>\n",
       "      <td>Residential</td>\n",
       "      <td>550.57</td>\n",
       "      <td>5.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>2019</td>\n",
       "      <td>Liberia</td>\n",
       "      <td>Monrovia</td>\n",
       "      <td>Western</td>\n",
       "      <td>Residential</td>\n",
       "      <td>306.96</td>\n",
       "      <td>9.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>2019</td>\n",
       "      <td>Burkina Faso</td>\n",
       "      <td>Ouagadougou</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>5.47</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>2019</td>\n",
       "      <td>Congo</td>\n",
       "      <td>Brazzaville</td>\n",
       "      <td>Middle</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>498.92</td>\n",
       "      <td>-4.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>264 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       日期                 国家           城市        地区         服务分类     销售额  \\\n",
       "0    2019             Angola       Luanda    Middle   Commercial  421.39   \n",
       "1    2019           Cameroon       Douala    Middle       Public  215.26   \n",
       "2    2019           Botswana     Gaborone  Southern   Commercial  471.08   \n",
       "3    2019              Egypt        Cairo  Northern       Public  435.44   \n",
       "4    2019  Equatorial Guinea       Malabo    Middle  Residential  207.77   \n",
       "..    ...                ...          ...       ...          ...     ...   \n",
       "259  2019             Malawi     Blantyre   Eastern  Residential  692.45   \n",
       "260  2019            Nigeria         Kano   Western  Residential  550.57   \n",
       "261  2019            Liberia     Monrovia   Western  Residential  306.96   \n",
       "262  2019       Burkina Faso  Ouagadougou   Western   Commercial    5.47   \n",
       "263  2019              Congo  Brazzaville    Middle   Commercial  498.92   \n",
       "\n",
       "        利润  \n",
       "0   -25.28  \n",
       "1     8.61  \n",
       "2     0.00  \n",
       "3    21.77  \n",
       "4     6.23  \n",
       "..     ...  \n",
       "259   6.92  \n",
       "260   5.51  \n",
       "261   9.21  \n",
       "262   0.55  \n",
       "263  -4.99  \n",
       "\n",
       "[264 rows x 7 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#年度利润销售额\n",
    "af2017=pd.read_excel('./data/2017.xlsx')\n",
    "af2018=pd.read_excel('./data/2018.xlsx')\n",
    "af2019=pd.read_excel('./data/2019.xlsx')\n",
    "af2020=pd.read_excel('./data/2020.xlsx')\n",
    "\n",
    "af2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 264 entries, 0 to 263\n",
      "Data columns (total 7 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   日期      264 non-null    int64  \n",
      " 1   国家      264 non-null    object \n",
      " 2   城市      264 non-null    object \n",
      " 3   地区      264 non-null    object \n",
      " 4   服务分类    264 non-null    object \n",
      " 5   销售额     264 non-null    float64\n",
      " 6   利润      264 non-null    float64\n",
      "dtypes: float64(2), int64(1), object(4)\n",
      "memory usage: 14.6+ KB\n"
     ]
    }
   ],
   "source": [
    "af2017.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>地区</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017</td>\n",
       "      <td>Cote d'Ivoire</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>656.96</td>\n",
       "      <td>6.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>875.94</td>\n",
       "      <td>-70.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>258.35</td>\n",
       "      <td>18.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>875.62</td>\n",
       "      <td>-35.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>509.93</td>\n",
       "      <td>10.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>2017</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>Southern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>819.25</td>\n",
       "      <td>24.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260</th>\n",
       "      <td>2017</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>231.41</td>\n",
       "      <td>-4.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>2017</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>Western</td>\n",
       "      <td>Public</td>\n",
       "      <td>572.13</td>\n",
       "      <td>17.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>2017</td>\n",
       "      <td>Benin</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>431.00</td>\n",
       "      <td>-21.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>2017</td>\n",
       "      <td>Mozambique</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>183.56</td>\n",
       "      <td>-7.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>264 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       日期             国家        地区         服务分类     销售额     利润\n",
       "0    2017  Cote d'Ivoire   Western   Commercial  656.96   6.57\n",
       "1    2017     Madagascar   Eastern       Public  875.94 -70.08\n",
       "2    2017         Rwanda   Eastern       Public  258.35  18.08\n",
       "3    2017       Zimbabwe   Eastern  Residential  875.62 -35.02\n",
       "4    2017       Ethiopia   Eastern  Residential  509.93  10.20\n",
       "..    ...            ...       ...          ...     ...    ...\n",
       "259  2017   South Africa  Southern  Residential  819.25  24.58\n",
       "260  2017       Zimbabwe   Eastern   Commercial  231.41  -4.63\n",
       "261  2017        Nigeria   Western       Public  572.13  17.16\n",
       "262  2017          Benin   Western   Commercial  431.00 -21.55\n",
       "263  2017     Mozambique   Eastern       Public  183.56  -7.34\n",
       "\n",
       "[264 rows x 6 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "af2017.drop('城市',axis=1,inplace=True)\n",
    "af2018.drop('城市',axis=1,inplace=True)\n",
    "af2019.drop('城市',axis=1,inplace=True)\n",
    "af2020.drop('城市',axis=1,inplace=True)\n",
    "af2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">2017</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Algeria</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Northern</th>\n",
       "      <td>1575.74</td>\n",
       "      <td>2.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Northern</th>\n",
       "      <td>1292.01</td>\n",
       "      <td>-21.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Angola</th>\n",
       "      <th>Public</th>\n",
       "      <th>Middle</th>\n",
       "      <td>670.01</td>\n",
       "      <td>47.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Middle</th>\n",
       "      <td>781.45</td>\n",
       "      <td>76.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Benin</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Western</th>\n",
       "      <td>2099.60</td>\n",
       "      <td>46.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Zambia</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>222.26</td>\n",
       "      <td>20.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>1105.65</td>\n",
       "      <td>-39.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>627.11</td>\n",
       "      <td>37.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Zimbabwe</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>231.41</td>\n",
       "      <td>-4.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>1718.81</td>\n",
       "      <td>-70.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>130 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        销售额     利润\n",
       "日期   国家       服务分类        地区                      \n",
       "2017 Algeria  Commercial  Northern  1575.74   2.08\n",
       "              Residential Northern  1292.01 -21.80\n",
       "     Angola   Public      Middle     670.01  47.53\n",
       "              Residential Middle     781.45  76.29\n",
       "     Benin    Commercial  Western   2099.60  46.72\n",
       "...                                     ...    ...\n",
       "     Zambia   Commercial  Eastern    222.26  20.00\n",
       "              Public      Eastern   1105.65 -39.80\n",
       "              Residential Eastern    627.11  37.63\n",
       "     Zimbabwe Commercial  Eastern    231.41  -4.63\n",
       "              Residential Eastern   1718.81 -70.54\n",
       "\n",
       "[130 rows x 2 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "sales=af2017.groupby(by=['日期','国家','服务分类','地区'])['销售额'].sum()\n",
    "profit=af2017.groupby(by=['日期','国家','服务分类','地区'])['利润'].sum()\n",
    "task1_2017=pd.concat((sales,profit),axis=1,join='inner')\n",
    "task1_2017\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">2018</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Algeria</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Northern</th>\n",
       "      <td>1166.28</td>\n",
       "      <td>69.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Northern</th>\n",
       "      <td>902.45</td>\n",
       "      <td>-9.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Angola</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Middle</th>\n",
       "      <td>737.95</td>\n",
       "      <td>-23.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Middle</th>\n",
       "      <td>624.65</td>\n",
       "      <td>-39.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Benin</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Western</th>\n",
       "      <td>2179.07</td>\n",
       "      <td>-35.12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Western Sahara</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Northern</th>\n",
       "      <td>216.43</td>\n",
       "      <td>-10.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Zambia</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>371.36</td>\n",
       "      <td>-29.71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>1351.24</td>\n",
       "      <td>-26.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Zimbabwe</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>1734.71</td>\n",
       "      <td>5.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>979.48</td>\n",
       "      <td>0.69</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>119 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              销售额     利润\n",
       "日期   国家             服务分类        地区                      \n",
       "2018 Algeria        Commercial  Northern  1166.28  69.54\n",
       "                    Public      Northern   902.45  -9.02\n",
       "     Angola         Commercial  Middle     737.95 -23.98\n",
       "                    Public      Middle     624.65 -39.62\n",
       "     Benin          Commercial  Western   2179.07 -35.12\n",
       "...                                           ...    ...\n",
       "     Western Sahara Residential Northern   216.43 -10.82\n",
       "     Zambia         Commercial  Eastern    371.36 -29.71\n",
       "                    Residential Eastern   1351.24 -26.59\n",
       "     Zimbabwe       Commercial  Eastern   1734.71   5.52\n",
       "                    Public      Eastern    979.48   0.69\n",
       "\n",
       "[119 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales=af2018.groupby(by=['日期','国家','服务分类','地区'])['销售额'].sum()\n",
    "profit=af2018.groupby(by=['日期','国家','服务分类','地区'])['利润'].sum()\n",
    "task1_2018=pd.concat((sales,profit),axis=1,join='inner') \n",
    "task1_2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">2019</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Algeria</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Northern</th>\n",
       "      <td>477.01</td>\n",
       "      <td>-4.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Northern</th>\n",
       "      <td>704.65</td>\n",
       "      <td>34.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Angola</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Middle</th>\n",
       "      <td>1124.46</td>\n",
       "      <td>9.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Middle</th>\n",
       "      <td>39.80</td>\n",
       "      <td>-3.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Benin</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Western</th>\n",
       "      <td>2056.53</td>\n",
       "      <td>19.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Zambia</th>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>1128.39</td>\n",
       "      <td>35.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>820.85</td>\n",
       "      <td>57.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Zimbabwe</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>1179.71</td>\n",
       "      <td>-44.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>574.87</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>438.85</td>\n",
       "      <td>26.33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>127 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        销售额     利润\n",
       "日期   国家       服务分类        地区                      \n",
       "2019 Algeria  Commercial  Northern   477.01  -4.77\n",
       "              Residential Northern   704.65  34.69\n",
       "     Angola   Commercial  Middle    1124.46   9.33\n",
       "              Public      Middle      39.80  -3.18\n",
       "     Benin    Commercial  Western   2056.53  19.07\n",
       "...                                     ...    ...\n",
       "     Zambia   Public      Eastern   1128.39  35.17\n",
       "              Residential Eastern    820.85  57.46\n",
       "     Zimbabwe Commercial  Eastern   1179.71 -44.94\n",
       "              Public      Eastern    574.87   0.00\n",
       "              Residential Eastern    438.85  26.33\n",
       "\n",
       "[127 rows x 2 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales=af2019.groupby(by=['日期','国家','服务分类','地区'])['销售额'].sum()\n",
    "profit=af2019.groupby(by=['日期','国家','服务分类','地区'])['利润'].sum()\n",
    "task1_2019=pd.concat((sales,profit),axis=1,join='inner') \n",
    "task1_2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"11\" valign=\"top\">2020</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Algeria</th>\n",
       "      <th>Public</th>\n",
       "      <th>Northern</th>\n",
       "      <td>695.64</td>\n",
       "      <td>8.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Northern</th>\n",
       "      <td>1528.15</td>\n",
       "      <td>-58.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Angola</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Middle</th>\n",
       "      <td>878.19</td>\n",
       "      <td>61.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Middle</th>\n",
       "      <td>398.18</td>\n",
       "      <td>-19.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Middle</th>\n",
       "      <td>826.40</td>\n",
       "      <td>41.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Zambia</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>1539.77</td>\n",
       "      <td>-39.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>84.03</td>\n",
       "      <td>-3.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Zimbabwe</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>401.79</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>179.71</td>\n",
       "      <td>8.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>843.98</td>\n",
       "      <td>-23.66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>135 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        销售额     利润\n",
       "日期   国家       服务分类        地区                      \n",
       "2020 Algeria  Public      Northern   695.64   8.61\n",
       "              Residential Northern  1528.15 -58.87\n",
       "     Angola   Commercial  Middle     878.19  61.47\n",
       "              Public      Middle     398.18 -19.91\n",
       "              Residential Middle     826.40  41.41\n",
       "...                                     ...    ...\n",
       "     Zambia   Commercial  Eastern   1539.77 -39.97\n",
       "              Public      Eastern     84.03  -3.08\n",
       "     Zimbabwe Commercial  Eastern    401.79   0.00\n",
       "              Public      Eastern    179.71   8.99\n",
       "              Residential Eastern    843.98 -23.66\n",
       "\n",
       "[135 rows x 2 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales=af2020.groupby(by=['日期','国家','服务分类','地区'])['销售额'].sum()\n",
    "profit=af2020.groupby(by=['日期','国家','服务分类','地区'])['利润'].sum()\n",
    "task1_2020=pd.concat((sales,profit),axis=1,join='inner') \n",
    "task1_2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2020</th>\n",
       "      <th>South Africa</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Southern</th>\n",
       "      <td>3566.00</td>\n",
       "      <td>-130.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nigeria</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Western</th>\n",
       "      <td>3075.46</td>\n",
       "      <td>39.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Democratic Republic of the Congo</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Middle</th>\n",
       "      <td>2955.70</td>\n",
       "      <td>-20.14</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                销售额      利润\n",
       "日期   国家                               服务分类        地区                       \n",
       "2020 South Africa                     Commercial  Southern  3566.00 -130.35\n",
       "     Nigeria                          Residential Western   3075.46   39.40\n",
       "     Democratic Republic of the Congo Commercial  Middle    2955.70  -20.14"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "task1_2020.sort_values(by='销售额',axis=0,ascending=False,inplace=True)\n",
    "task1_2020.head(3)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "task1=pd.concat((task1_2017,task1_2018,task1_2019,task1_2020),axis=0,join='outer') \n",
    "task1.to_excel(\"./../result/result1_1.xlsx\",encoding='gbk')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>城市</th>\n",
       "      <th>地区</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Cote d'Ivoire</td>\n",
       "      <td>Abidjan</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>656.96</td>\n",
       "      <td>6.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>Antananarivo</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>875.94</td>\n",
       "      <td>-70.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Kigali</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>258.35</td>\n",
       "      <td>18.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Harare</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>875.62</td>\n",
       "      <td>-35.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>Addis Ababa</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>509.93</td>\n",
       "      <td>10.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>Cape Town</td>\n",
       "      <td>Southern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>819.25</td>\n",
       "      <td>24.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Harare</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>231.41</td>\n",
       "      <td>-4.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>Kano</td>\n",
       "      <td>Western</td>\n",
       "      <td>Public</td>\n",
       "      <td>572.13</td>\n",
       "      <td>17.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>Benin</td>\n",
       "      <td>Cotonou</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>431.00</td>\n",
       "      <td>-21.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>Mozambique</td>\n",
       "      <td>Maputo</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>183.56</td>\n",
       "      <td>-7.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>264 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           日期             国家            城市        地区         服务分类     销售额  \\\n",
       "0    2017第一季度  Cote d'Ivoire       Abidjan   Western   Commercial  656.96   \n",
       "1    2017第一季度     Madagascar  Antananarivo   Eastern       Public  875.94   \n",
       "2    2017第一季度         Rwanda        Kigali   Eastern       Public  258.35   \n",
       "3    2017第一季度       Zimbabwe        Harare   Eastern  Residential  875.62   \n",
       "4    2017第一季度       Ethiopia   Addis Ababa   Eastern  Residential  509.93   \n",
       "..        ...            ...           ...       ...          ...     ...   \n",
       "259  2017第四季度   South Africa     Cape Town  Southern  Residential  819.25   \n",
       "260  2017第四季度       Zimbabwe        Harare   Eastern   Commercial  231.41   \n",
       "261  2017第四季度        Nigeria          Kano   Western       Public  572.13   \n",
       "262  2017第四季度          Benin       Cotonou   Western   Commercial  431.00   \n",
       "263  2017第四季度     Mozambique        Maputo   Eastern       Public  183.56   \n",
       "\n",
       "        利润  \n",
       "0     6.57  \n",
       "1   -70.08  \n",
       "2    18.08  \n",
       "3   -35.02  \n",
       "4    10.20  \n",
       "..     ...  \n",
       "259  24.58  \n",
       "260  -4.63  \n",
       "261  17.16  \n",
       "262 -21.55  \n",
       "263  -7.34  \n",
       "\n",
       "[264 rows x 7 columns]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#季度销售额\n",
    "af2017m=pd.read_excel('./data/2017m.xlsx')\n",
    "af2018m=pd.read_excel('./data/2018m.xlsx')\n",
    "af2019m=pd.read_excel('./data/2019m.xlsx')\n",
    "af2020m=pd.read_excel('./data/2020m.xlsx')\n",
    "af2017m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>地区</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Cote d'Ivoire</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>656.96</td>\n",
       "      <td>6.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>875.94</td>\n",
       "      <td>-70.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>258.35</td>\n",
       "      <td>18.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>875.62</td>\n",
       "      <td>-35.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017第一季度</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>509.93</td>\n",
       "      <td>10.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>Southern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>819.25</td>\n",
       "      <td>24.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>260</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>231.41</td>\n",
       "      <td>-4.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>Nigeria</td>\n",
       "      <td>Western</td>\n",
       "      <td>Public</td>\n",
       "      <td>572.13</td>\n",
       "      <td>17.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>262</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>Benin</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>431.00</td>\n",
       "      <td>-21.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>2017第四季度</td>\n",
       "      <td>Mozambique</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>183.56</td>\n",
       "      <td>-7.34</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>264 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           日期             国家        地区         服务分类     销售额     利润\n",
       "0    2017第一季度  Cote d'Ivoire   Western   Commercial  656.96   6.57\n",
       "1    2017第一季度     Madagascar   Eastern       Public  875.94 -70.08\n",
       "2    2017第一季度         Rwanda   Eastern       Public  258.35  18.08\n",
       "3    2017第一季度       Zimbabwe   Eastern  Residential  875.62 -35.02\n",
       "4    2017第一季度       Ethiopia   Eastern  Residential  509.93  10.20\n",
       "..        ...            ...       ...          ...     ...    ...\n",
       "259  2017第四季度   South Africa  Southern  Residential  819.25  24.58\n",
       "260  2017第四季度       Zimbabwe   Eastern   Commercial  231.41  -4.63\n",
       "261  2017第四季度        Nigeria   Western       Public  572.13  17.16\n",
       "262  2017第四季度          Benin   Western   Commercial  431.00 -21.55\n",
       "263  2017第四季度     Mozambique   Eastern       Public  183.56  -7.34\n",
       "\n",
       "[264 rows x 6 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "af2017m.drop('城市',axis=1,inplace=True)\n",
    "af2018m.drop('城市',axis=1,inplace=True)\n",
    "af2019m.drop('城市',axis=1,inplace=True)\n",
    "af2020m.drop('城市',axis=1,inplace=True)\n",
    "af2017m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2017第一季度</th>\n",
       "      <th>Algeria</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Northern</th>\n",
       "      <td>1292.01</td>\n",
       "      <td>-21.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Middle</th>\n",
       "      <td>93.20</td>\n",
       "      <td>7.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Benin</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Western</th>\n",
       "      <td>1282.29</td>\n",
       "      <td>88.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botswana</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Southern</th>\n",
       "      <td>660.12</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Burkina Faso</th>\n",
       "      <th>Public</th>\n",
       "      <th>Western</th>\n",
       "      <td>462.98</td>\n",
       "      <td>37.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2017第四季度</th>\n",
       "      <th>Togo</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Western</th>\n",
       "      <td>885.66</td>\n",
       "      <td>-70.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uganda</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>284.57</td>\n",
       "      <td>2.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Republic of Tanzania</th>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>434.34</td>\n",
       "      <td>34.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Western Sahara</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Northern</th>\n",
       "      <td>601.87</td>\n",
       "      <td>60.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zimbabwe</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>231.41</td>\n",
       "      <td>-4.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>213 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                               销售额     利润\n",
       "日期       国家                          服务分类        地区                      \n",
       "2017第一季度 Algeria                     Residential Northern  1292.01 -21.80\n",
       "         Angola                      Residential Middle      93.20   7.46\n",
       "         Benin                       Residential Western   1282.29  88.48\n",
       "         Botswana                    Commercial  Southern   660.12   0.00\n",
       "         Burkina Faso                Public      Western    462.98  37.04\n",
       "...                                                            ...    ...\n",
       "2017第四季度 Togo                        Commercial  Western    885.66 -70.85\n",
       "         Uganda                      Commercial  Eastern    284.57   2.85\n",
       "         United Republic of Tanzania Public      Eastern    434.34  34.75\n",
       "         Western Sahara              Commercial  Northern   601.87  60.19\n",
       "         Zimbabwe                    Commercial  Eastern    231.41  -4.63\n",
       "\n",
       "[213 rows x 2 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales=af2017m.groupby(by=['日期','国家','服务分类','地区'])['销售额'].sum()\n",
    "profit=af2017m.groupby(by=['日期','国家','服务分类','地区'])['利润'].sum()\n",
    "task1_2017m=pd.concat((sales,profit),axis=1,join='inner') \n",
    "task1_2017m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2018第一季度</th>\n",
       "      <th>Algeria</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Northern</th>\n",
       "      <td>743.65</td>\n",
       "      <td>52.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Middle</th>\n",
       "      <td>46.15</td>\n",
       "      <td>-3.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Benin</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Western</th>\n",
       "      <td>435.40</td>\n",
       "      <td>39.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <th>Western</th>\n",
       "      <td>628.33</td>\n",
       "      <td>56.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Western</th>\n",
       "      <td>343.36</td>\n",
       "      <td>27.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2018第四季度</th>\n",
       "      <th>Tunisia</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Northern</th>\n",
       "      <td>253.34</td>\n",
       "      <td>10.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uganda</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>696.60</td>\n",
       "      <td>69.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Republic of Tanzania</th>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>70.25</td>\n",
       "      <td>5.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Western Sahara</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Northern</th>\n",
       "      <td>216.43</td>\n",
       "      <td>-10.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zimbabwe</th>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>641.50</td>\n",
       "      <td>-12.83</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>212 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                              销售额     利润\n",
       "日期       国家                          服务分类        地区                     \n",
       "2018第一季度 Algeria                     Commercial  Northern  743.65  52.63\n",
       "         Angola                      Commercial  Middle     46.15  -3.23\n",
       "         Benin                       Commercial  Western   435.40  39.19\n",
       "                                     Public      Western   628.33  56.55\n",
       "                                     Residential Western   343.36  27.47\n",
       "...                                                           ...    ...\n",
       "2018第四季度 Tunisia                     Residential Northern  253.34  10.13\n",
       "         Uganda                      Residential Eastern   696.60  69.66\n",
       "         United Republic of Tanzania Public      Eastern    70.25   5.62\n",
       "         Western Sahara              Residential Northern  216.43 -10.82\n",
       "         Zimbabwe                    Public      Eastern   641.50 -12.83\n",
       "\n",
       "[212 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales=af2018m.groupby(by=['日期','国家','服务分类','地区'])['销售额'].sum()\n",
    "profit=af2018m.groupby(by=['日期','国家','服务分类','地区'])['利润'].sum()\n",
    "task1_2018m=pd.concat((sales,profit),axis=1,join='inner') \n",
    "task1_2018m\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2019第一季度</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">Algeria</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Northern</th>\n",
       "      <td>477.01</td>\n",
       "      <td>-4.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Northern</th>\n",
       "      <td>120.45</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Middle</th>\n",
       "      <td>421.39</td>\n",
       "      <td>-25.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Benin</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Western</th>\n",
       "      <td>686.65</td>\n",
       "      <td>44.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botswana</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Southern</th>\n",
       "      <td>471.08</td>\n",
       "      <td>0.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2019第四季度</th>\n",
       "      <th>Swaziland</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Southern</th>\n",
       "      <td>787.30</td>\n",
       "      <td>15.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tunisia</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Northern</th>\n",
       "      <td>728.79</td>\n",
       "      <td>72.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uganda</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>6.87</td>\n",
       "      <td>0.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Republic of Tanzania</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>505.64</td>\n",
       "      <td>-20.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zambia</th>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>12.31</td>\n",
       "      <td>-0.37</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>212 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                              销售额     利润\n",
       "日期       国家                          服务分类        地区                     \n",
       "2019第一季度 Algeria                     Commercial  Northern  477.01  -4.77\n",
       "                                     Residential Northern  120.45   0.00\n",
       "         Angola                      Commercial  Middle    421.39 -25.28\n",
       "         Benin                       Commercial  Western   686.65  44.64\n",
       "         Botswana                    Commercial  Southern  471.08   0.00\n",
       "...                                                           ...    ...\n",
       "2019第四季度 Swaziland                   Residential Southern  787.30  15.75\n",
       "         Tunisia                     Commercial  Northern  728.79  72.88\n",
       "         Uganda                      Residential Eastern     6.87   0.14\n",
       "         United Republic of Tanzania Commercial  Eastern   505.64 -20.23\n",
       "         Zambia                      Public      Eastern    12.31  -0.37\n",
       "\n",
       "[212 rows x 2 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales=af2019m.groupby(by=['日期','国家','服务分类','地区'])['销售额'].sum()\n",
    "profit=af2019m.groupby(by=['日期','国家','服务分类','地区'])['利润'].sum()\n",
    "task1_2019m=pd.concat((sales,profit),axis=1,join='inner') \n",
    "task1_2019m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2020第一季度</th>\n",
       "      <th>Algeria</th>\n",
       "      <th>Public</th>\n",
       "      <th>Northern</th>\n",
       "      <td>450.44</td>\n",
       "      <td>13.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Angola</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Middle</th>\n",
       "      <td>460.06</td>\n",
       "      <td>41.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Benin</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Western</th>\n",
       "      <td>389.61</td>\n",
       "      <td>15.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <th>Western</th>\n",
       "      <td>186.23</td>\n",
       "      <td>14.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Botswana</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Southern</th>\n",
       "      <td>117.65</td>\n",
       "      <td>3.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">2020第四季度</th>\n",
       "      <th>Tunisia</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Northern</th>\n",
       "      <td>665.22</td>\n",
       "      <td>33.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Uganda</th>\n",
       "      <th>Public</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>680.17</td>\n",
       "      <td>68.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>United Republic of Tanzania</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>859.82</td>\n",
       "      <td>-60.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zambia</th>\n",
       "      <th>Commercial</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>791.05</td>\n",
       "      <td>-47.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Zimbabwe</th>\n",
       "      <th>Residential</th>\n",
       "      <th>Eastern</th>\n",
       "      <td>394.28</td>\n",
       "      <td>-23.66</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>219 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                              销售额     利润\n",
       "日期       国家                          服务分类        地区                     \n",
       "2020第一季度 Algeria                     Public      Northern  450.44  13.51\n",
       "         Angola                      Residential Middle    460.06  41.41\n",
       "         Benin                       Commercial  Western   389.61  15.58\n",
       "                                     Residential Western   186.23  14.90\n",
       "         Botswana                    Commercial  Southern  117.65   3.53\n",
       "...                                                           ...    ...\n",
       "2020第四季度 Tunisia                     Residential Northern  665.22  33.26\n",
       "         Uganda                      Public      Eastern   680.17  68.02\n",
       "         United Republic of Tanzania Residential Eastern   859.82 -60.19\n",
       "         Zambia                      Commercial  Eastern   791.05 -47.46\n",
       "         Zimbabwe                    Residential Eastern   394.28 -23.66\n",
       "\n",
       "[219 rows x 2 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales=af2020m.groupby(by=['日期','国家','服务分类','地区'])['销售额'].sum()\n",
    "profit=af2020m.groupby(by=['日期','国家','服务分类','地区'])['利润'].sum()\n",
    "task1_2020m=pd.concat((sales,profit),axis=1,join='inner') \n",
    "task1_2020m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "task1_m=pd.concat((task1_2017m,task1_2018m,task1_2019m,task1_2020m),axis=0,join='outer') \n",
    "task1_m.to_excel('./../result/result1_1_2.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>国家</th>\n",
       "      <th>服务分类</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Algeria</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>3219.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>1598.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>3524.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Angola</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>2740.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>1732.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Zambia</th>\n",
       "      <th>Public</th>\n",
       "      <td>2318.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>2799.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Zimbabwe</th>\n",
       "      <th>Commercial</th>\n",
       "      <td>3547.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Public</th>\n",
       "      <td>1734.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residential</th>\n",
       "      <td>3001.64</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>156 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                          销售额\n",
       "国家       服务分类                \n",
       "Algeria  Commercial   3219.03\n",
       "         Public       1598.09\n",
       "         Residential  3524.81\n",
       "Angola   Commercial   2740.60\n",
       "         Public       1732.64\n",
       "...                       ...\n",
       "Zambia   Public       2318.07\n",
       "         Residential  2799.20\n",
       "Zimbabwe Commercial   3547.62\n",
       "         Public       1734.06\n",
       "         Residential  3001.64\n",
       "\n",
       "[156 rows x 1 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#求各各国各服务分类销售额\n",
    "data_sales_conuntry_all = task1_m.groupby(['国家','服务分类']).sum()\n",
    "data_sales_conuntry_all = data_sales_conuntry_all.drop('利润',axis=1)\n",
    "data_sales_conuntry_all"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-32-76d02c80fe6d>:20: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  df_rate = (float)((df18.loc[i])[1] - (df17.loc[j])[1])/(df17.loc[j])[1]\n"
     ]
    }
   ],
   "source": [
    "#计算各国/各服务分类的利润的同比增长率\n",
    "df17 = af2017m.loc[af2017m['日期'] == '2017第一季度',['国家','利润']]\n",
    "df18 = af2018m.loc[af2018m['日期'] == '2018第一季度',['国家','利润']]\n",
    "df19 = af2019m.loc[af2019m['日期'] == '2019第一季度',['国家','利润']]\n",
    "df20 = af2020m.loc[af2020m['日期'] == '2020第一季度',['国家','利润']]\n",
    "# if (df18[i] == df17[j]):\n",
    "#     df18_rate = df18 - df17\n",
    "i = 1\n",
    "j = 1\n",
    "df_18_all_rate_1 = df18\n",
    "df_18_all_rate_1['利润的同比增长率'] = ''\n",
    "df_18_all_rate_1\n",
    "df_rate_sum = []\n",
    "for i in range(len(df18)):\n",
    "    for j in range(len(df17)):\n",
    "        df_rate = 0\n",
    "        if ((df18.loc[i])[0] == (df17.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df18.loc[i])[1] - (df17.loc[j])[1])/(df17.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_18_all_rate_1['利润的同比增长率'] = df_rate_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-33-8c4179689b08>:13: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  df_rate = (float)((df19.loc[k])[1] - (df18.loc[l])[1])/(df18.loc[l])[1]\n"
     ]
    }
   ],
   "source": [
    "i = 1\n",
    "j = 1\n",
    "df_19_all_rate_1 = df19\n",
    "df_19_all_rate_1['利润的同比增长率'] = ''\n",
    "df_19_all_rate_1\n",
    "df_rate_sum = []\n",
    "for k in range(len(df19)):\n",
    "    for l in range(len(df18)):\n",
    "        df_rate = 0\n",
    "        if ((df19.loc[k])[0] == (df18.loc[l])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df19.loc[k])[1] - (df18.loc[l])[1])/(df18.loc[l])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_19_all_rate_1['利润的同比增长率'] = df_rate_sum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-34-ded208ab9ec7>:13: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  df_rate = (float)((df20.loc[m])[1] - (df19.loc[n])[1])/(df19.loc[n])[1]\n"
     ]
    }
   ],
   "source": [
    "i = 1\n",
    "j = 1\n",
    "df_20_all_rate_1 = df20\n",
    "df_20_all_rate_1['利润的同比增长率'] = ''\n",
    "df_20_all_rate_1\n",
    "df_rate_sum = []\n",
    "for m in range(len(df20)):\n",
    "    for n in range(len(df19)):\n",
    "        df_rate = 0\n",
    "        if ((df20.loc[m])[0] == (df19.loc[n])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df20.loc[m])[1] - (df19.loc[n])[1])/(df19.loc[n])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_20_all_rate_1['利润的同比增长率'] = df_rate_sum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-35-f899c22856c0>:23: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  df_rate = (float)((df18.loc[i])[1] - (df17.loc[j])[1])/(df17.loc[j])[1]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "df17 = af2017m.loc[af2017m['日期'] == '2017第二季度',['国家','利润']]\n",
    "df18 = af2018m.loc[af2018m['日期'] == '2018第二季度',['国家','利润']]\n",
    "df19 = af2019m.loc[af2019m['日期'] == '2019第二季度',['国家','利润']]\n",
    "df20 = af2020m.loc[af2020m['日期'] == '2020第二季度',['国家','利润']]\n",
    "df17 = df17.reset_index(drop=True)\n",
    "df18 = df18.reset_index(drop=True)\n",
    "df19 = df19.reset_index(drop=True)\n",
    "df20 = df20.reset_index(drop=True)\n",
    "# if (df18[i] == df17[j]):\n",
    "#     df18_rate = df18 - df17\n",
    "i = 1\n",
    "j = 1\n",
    "df_18_all_rate_2 = df18\n",
    "df_18_all_rate_2['利润的同比增长率'] = ''\n",
    "df_18_all_rate_2\n",
    "df_rate_sum = []\n",
    "for i in range(len(df18)):\n",
    "    for j in range(len(df17)):\n",
    "        df_rate = 0\n",
    "        if ((df18.loc[i])[0] == (df17.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df18.loc[i])[1] - (df17.loc[j])[1])/(df17.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_18_all_rate_2['利润的同比增长率'] = df_rate_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "i = 1\n",
    "j = 1\n",
    "df_19_all_rate_2 = df19\n",
    "df_19_all_rate_2['利润的同比增长率'] = ''\n",
    "df_19_all_rate_2\n",
    "df_rate_sum = []\n",
    "for i in range(len(df19)):\n",
    "    for j in range(len(df18)):\n",
    "        df_rate = 0\n",
    "        if ((df19.loc[i])[0] == (df18.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df19.loc[i])[1] - (df18.loc[j])[1])/(df18.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_19_all_rate_2['利润的同比增长率'] = df_rate_sum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "i = 1\n",
    "j = 1\n",
    "df_20_all_rate_2 = df20\n",
    "df_20_all_rate_2['利润的同比增长率'] = ''\n",
    "df_20_all_rate_2\n",
    "df_rate_sum = []\n",
    "for i in range(len(df20)):\n",
    "    for j in range(len(df19)):\n",
    "        df_rate = 0\n",
    "        if ((df20.loc[i])[0] == (df19.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df20.loc[i])[1] - (df19.loc[j])[1])/(df19.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_20_all_rate_2['利润的同比增长率'] = df_rate_sum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df17 = af2017m.loc[af2017m['日期'] == '2017第三季度',['国家','利润']]\n",
    "df18 = af2018m.loc[af2018m['日期'] == '2018第三季度',['国家','利润']]\n",
    "df19 = af2019m.loc[af2019m['日期'] == '2019第三季度',['国家','利润']]\n",
    "df20 = af2020m.loc[af2020m['日期'] == '2020第三季度',['国家','利润']]\n",
    "df17 = df17.reset_index(drop=True)\n",
    "df18 = df18.reset_index(drop=True)\n",
    "df19 = df19.reset_index(drop=True)\n",
    "df20 = df20.reset_index(drop=True)\n",
    "# if (df18[i] == df17[j]):\n",
    "#     df18_rate = df18 - df17\n",
    "i = 1\n",
    "j = 1\n",
    "df_18_all_rate_3 = df18\n",
    "df_18_all_rate_3['利润的同比增长率'] = ''\n",
    "df_18_all_rate_3\n",
    "df_rate_sum = []\n",
    "for i in range(len(df18)):\n",
    "    for j in range(len(df17)):\n",
    "        df_rate = 0\n",
    "        if ((df18.loc[i])[0] == (df17.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df18.loc[i])[1] - (df17.loc[j])[1])/(df17.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_18_all_rate_3['利润的同比增长率'] = df_rate_sum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-39-091b593a9213>:13: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  df_rate = (float)((df19.loc[i])[1] - (df18.loc[j])[1])/(df18.loc[j])[1]\n"
     ]
    }
   ],
   "source": [
    "i = 1\n",
    "j = 1\n",
    "df_19_all_rate_3 = df19\n",
    "df_19_all_rate_3['利润的同比增长率'] = ''\n",
    "df_19_all_rate_3\n",
    "df_rate_sum = []\n",
    "for i in range(len(df19)):\n",
    "    for j in range(len(df18)):\n",
    "        df_rate = 0\n",
    "        if ((df19.loc[i])[0] == (df18.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df19.loc[i])[1] - (df18.loc[j])[1])/(df18.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_19_all_rate_3['利润的同比增长率'] = df_rate_sum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-40-7b51d52ec425>:13: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  df_rate = (float)((df20.loc[i])[1] - (df19.loc[j])[1])/(df19.loc[j])[1]\n"
     ]
    }
   ],
   "source": [
    "i = 1\n",
    "j = 1\n",
    "df_20_all_rate_3 = df20\n",
    "df_20_all_rate_3['利润的同比增长率'] = ''\n",
    "df_20_all_rate_3\n",
    "df_rate_sum = []\n",
    "for i in range(len(df20)):\n",
    "    for j in range(len(df19)):\n",
    "        df_rate = 0\n",
    "        if ((df20.loc[i])[0] == (df19.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df20.loc[i])[1] - (df19.loc[j])[1])/(df19.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_20_all_rate_3['利润的同比增长率'] = df_rate_sum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df17 = af2017m.loc[af2017m['日期'] == '2017第四季度',['国家','利润']]\n",
    "df18 = af2018m.loc[af2018m['日期'] == '2018第四季度',['国家','利润']]\n",
    "df19 = af2019m.loc[af2019m['日期'] == '2019第四季度',['国家','利润']]\n",
    "df20 = af2020m.loc[af2020m['日期'] == '2020第四季度',['国家','利润']]\n",
    "df17 = df17.reset_index(drop=True)\n",
    "df18 = df18.reset_index(drop=True)\n",
    "df19 = df19.reset_index(drop=True)\n",
    "df20 = df20.reset_index(drop=True)\n",
    "# if (df18[i] == df17[j]):\n",
    "#     df18_rate = df18 - df17\n",
    "i = 1\n",
    "j = 1\n",
    "df_18_all_rate_4 = df18\n",
    "df_18_all_rate_4['利润的同比增长率'] = ''\n",
    "df_18_all_rate_4\n",
    "df_rate_sum = []\n",
    "for i in range(len(df18)):\n",
    "    for j in range(len(df17)):\n",
    "        df_rate = 0\n",
    "        if ((df18.loc[i])[0] == (df17.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df18.loc[i])[1] - (df17.loc[j])[1])/(df17.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_18_all_rate_4['利润的同比增长率'] = df_rate_sum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-42-9fc9424b99eb>:13: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  df_rate = (float)((df19.loc[i])[1] - (df18.loc[j])[1])/(df18.loc[j])[1]\n"
     ]
    }
   ],
   "source": [
    "i = 1\n",
    "j = 1\n",
    "df_19_all_rate_4 = df19\n",
    "df_19_all_rate_4['利润的同比增长率'] = ''\n",
    "df_19_all_rate_4\n",
    "df_rate_sum = []\n",
    "for i in range(len(df19)):\n",
    "    for j in range(len(df18)):\n",
    "        df_rate = 0\n",
    "        if ((df19.loc[i])[0] == (df18.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df19.loc[i])[1] - (df18.loc[j])[1])/(df18.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_19_all_rate_4['利润的同比增长率'] = df_rate_sum\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-44-87928e0d6581>:13: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "  df_rate = (float)((df20.loc[i])[1] - (df19.loc[j])[1])/(df19.loc[j])[1]\n"
     ]
    }
   ],
   "source": [
    "i = 1\n",
    "j = 1\n",
    "df_20_all_rate_4 = df20\n",
    "df_20_all_rate_4['利润的同比增长率'] = ''\n",
    "df_20_all_rate_4\n",
    "df_rate_sum = []\n",
    "for i in range(len(df20)):\n",
    "    for j in range(len(df19)):\n",
    "        df_rate = 0\n",
    "        if ((df20.loc[i])[0] == (df19.loc[j])[0]):\n",
    "            #求同比增长率\n",
    "            try:\n",
    "                df_rate = (float)((df20.loc[i])[1] - (df19.loc[j])[1])/(df19.loc[j])[1]\n",
    "            except:\n",
    "                df_rate = 0\n",
    "            break\n",
    "\n",
    "    df_rate_sum.insert(-1,df_rate)\n",
    "df_20_all_rate_4['利润的同比增长率'] = df_rate_sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "#合并利润同比增长率表\n",
    "df_18_all_rate_1['时间'] = '2018第一季度'\n",
    "df_18_all_rate_2['时间'] = '2019第二季度'\n",
    "df_18_all_rate_3['时间'] = '2020第三季度'\n",
    "df_18_all_rate_4['时间'] = '2018第四季度'\n",
    "df_19_all_rate_1['时间'] = '2019第一季度'\n",
    "df_19_all_rate_2['时间'] = '2020第二季度'\n",
    "df_19_all_rate_3['时间'] = '2018第三季度'\n",
    "df_19_all_rate_4['时间'] = '2019第四季度'\n",
    "df_20_all_rate_1['时间'] = '2020第一季度'\n",
    "df_20_all_rate_2['时间'] = '2018第二季度'\n",
    "df_20_all_rate_3['时间'] = '2019第三季度'\n",
    "df_20_all_rate_4['时间'] = '2020第四季度'\n",
    "df_rate_All = pd.concat([df_18_all_rate_1,df_18_all_rate_2,df_18_all_rate_3,df_18_all_rate_4,df_19_all_rate_1,df_19_all_rate_2,df_19_all_rate_3,df_19_all_rate_4,df_20_all_rate_1,df_20_all_rate_2,df_20_all_rate_3,df_20_all_rate_4])\n",
    "df_rate_All.to_excel('./../result/result1_1_3.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务1.2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>国家</th>\n",
       "      <th>城市</th>\n",
       "      <th>地区</th>\n",
       "      <th>服务分类</th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Cote d'Ivoire</td>\n",
       "      <td>Abidjan</td>\n",
       "      <td>Western</td>\n",
       "      <td>Commercial</td>\n",
       "      <td>656.96</td>\n",
       "      <td>6.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>Antananarivo</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>875.94</td>\n",
       "      <td>-70.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Kigali</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Public</td>\n",
       "      <td>258.35</td>\n",
       "      <td>18.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Harare</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>875.62</td>\n",
       "      <td>-35.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-02</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>Addis Ababa</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>Residential</td>\n",
       "      <td>509.93</td>\n",
       "      <td>10.20</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          日期             国家            城市       地区         服务分类     销售额     利润\n",
       "0 2017-01-01  Cote d'Ivoire       Abidjan  Western   Commercial  656.96   6.57\n",
       "1 2017-01-01     Madagascar  Antananarivo  Eastern       Public  875.94 -70.08\n",
       "2 2017-01-01         Rwanda        Kigali  Eastern       Public  258.35  18.08\n",
       "3 2017-01-01       Zimbabwe        Harare  Eastern  Residential  875.62 -35.02\n",
       "4 2017-01-02       Ethiopia   Addis Ababa  Eastern  Residential  509.93  10.20"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import DataFrame,Series\n",
    "from datetime import datetime\n",
    "data1 = pd.read_excel(\"./data/非洲通讯产品销售数据.xlsx\",0)\n",
    "data2 = pd.read_excel(\"./data/非洲通讯产品销售数据.xlsx\",1,usecols=[0,1,2,3,4])\n",
    "data1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>服务分类</th>\n",
       "      <th>国家</th>\n",
       "      <th>地区</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Commercial</td>\n",
       "      <td>Cote d'Ivoire</td>\n",
       "      <td>Western</td>\n",
       "      <td>656.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Public</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>875.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Public</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>258.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>875.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>509.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1051</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>394.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1052</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Malawi</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>333.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1053</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Liberia</td>\n",
       "      <td>Western</td>\n",
       "      <td>133.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1054</th>\n",
       "      <td>Public</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>737.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1055</th>\n",
       "      <td>Residential</td>\n",
       "      <td>United Republic of Tanzania</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>859.82</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1056 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             服务分类                           国家       地区     销售额\n",
       "0      Commercial                Cote d'Ivoire  Western  656.96\n",
       "1          Public                   Madagascar  Eastern  875.94\n",
       "2          Public                       Rwanda  Eastern  258.35\n",
       "3     Residential                     Zimbabwe  Eastern  875.62\n",
       "4     Residential                     Ethiopia  Eastern  509.93\n",
       "...           ...                          ...      ...     ...\n",
       "1051  Residential                     Zimbabwe  Eastern  394.28\n",
       "1052  Residential                       Malawi  Eastern  333.07\n",
       "1053  Residential                      Liberia  Western  133.26\n",
       "1054       Public                        Kenya  Eastern  737.57\n",
       "1055  Residential  United Republic of Tanzania  Eastern  859.82\n",
       "\n",
       "[1056 rows x 4 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_sales = data1[['服务分类','国家','地区','销售额']]\n",
    "data_sales"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>服务分类</th>\n",
       "      <th>国家</th>\n",
       "      <th>地区</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Commercial</td>\n",
       "      <td>Cote d'Ivoire</td>\n",
       "      <td>Western</td>\n",
       "      <td>6.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Public</td>\n",
       "      <td>Madagascar</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>-70.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Public</td>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>18.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>-35.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Ethiopia</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>10.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1051</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>-23.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1052</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Malawi</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>-16.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1053</th>\n",
       "      <td>Residential</td>\n",
       "      <td>Liberia</td>\n",
       "      <td>Western</td>\n",
       "      <td>-8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1054</th>\n",
       "      <td>Public</td>\n",
       "      <td>Kenya</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>36.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1055</th>\n",
       "      <td>Residential</td>\n",
       "      <td>United Republic of Tanzania</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>-60.19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1056 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             服务分类                           国家       地区     利润\n",
       "0      Commercial                Cote d'Ivoire  Western   6.57\n",
       "1          Public                   Madagascar  Eastern -70.08\n",
       "2          Public                       Rwanda  Eastern  18.08\n",
       "3     Residential                     Zimbabwe  Eastern -35.02\n",
       "4     Residential                     Ethiopia  Eastern  10.20\n",
       "...           ...                          ...      ...    ...\n",
       "1051  Residential                     Zimbabwe  Eastern -23.66\n",
       "1052  Residential                       Malawi  Eastern -16.65\n",
       "1053  Residential                      Liberia  Western  -8.00\n",
       "1054       Public                        Kenya  Eastern  36.88\n",
       "1055  Residential  United Republic of Tanzania  Eastern -60.19\n",
       "\n",
       "[1056 rows x 4 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_profit =data1[['服务分类','国家','地区','利润']]\n",
    "data_profit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Western     320\n",
       "Eastern     288\n",
       "Middle      192\n",
       "Southern    128\n",
       "Northern    128\n",
       "Name: 地区, dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 地区出现的频次\n",
    "data_sales['地区'].value_counts(dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>销售额</th>\n",
       "      <th>利润</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>服务分类</th>\n",
       "      <th>地区</th>\n",
       "      <th>国家</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Commercial</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Eastern</th>\n",
       "      <th>Burundi</th>\n",
       "      <td>1228.37</td>\n",
       "      <td>59.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Djibouti</th>\n",
       "      <td>1262.47</td>\n",
       "      <td>-34.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Eritrea</th>\n",
       "      <td>5132.64</td>\n",
       "      <td>118.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ethiopia</th>\n",
       "      <td>5442.21</td>\n",
       "      <td>207.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Kenya</th>\n",
       "      <td>759.18</td>\n",
       "      <td>-15.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">Residential</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">Western</th>\n",
       "      <th>Niger</th>\n",
       "      <td>2279.28</td>\n",
       "      <td>5.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Nigeria</th>\n",
       "      <td>9594.12</td>\n",
       "      <td>145.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Senegal</th>\n",
       "      <td>770.62</td>\n",
       "      <td>19.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sierra Leone</th>\n",
       "      <td>1694.66</td>\n",
       "      <td>-7.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Togo</th>\n",
       "      <td>1782.39</td>\n",
       "      <td>68.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>156 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      销售额      利润\n",
       "服务分类        地区      国家                           \n",
       "Commercial  Eastern Burundi       1228.37   59.18\n",
       "                    Djibouti      1262.47  -34.11\n",
       "                    Eritrea       5132.64  118.30\n",
       "                    Ethiopia      5442.21  207.20\n",
       "                    Kenya          759.18  -15.18\n",
       "...                                   ...     ...\n",
       "Residential Western Niger         2279.28    5.14\n",
       "                    Nigeria       9594.12  145.84\n",
       "                    Senegal        770.62   19.21\n",
       "                    Sierra Leone  1694.66   -7.20\n",
       "                    Togo          1782.39   68.71\n",
       "\n",
       "[156 rows x 2 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_sales = data_sales.groupby(['服务分类','地区','国家']).sum()\n",
    "data_profit = data_profit.groupby(['服务分类','地区','国家']).sum()\n",
    "data_sales\n",
    "data_profit\n",
    "data_merge = pd.concat((data_sales,data_profit),axis=1,join='inner')\n",
    "data_merge.to_excel('./../result/result1_2.xlsx')\n",
    "data_merge"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 任务 1.3\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pandas import DataFrame,Series\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>销售经理</th>\n",
       "      <th>地区</th>\n",
       "      <th>销售合同</th>\n",
       "      <th>成交率</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>备注：本表格中“销售合同”为“已成交合同”。</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Aiden Morris</td>\n",
       "      <td>Western</td>\n",
       "      <td>13</td>\n",
       "      <td>0.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Audrey Baker</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>11</td>\n",
       "      <td>0.30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Constantine Eager</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>9</td>\n",
       "      <td>0.36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Francis Martineau</td>\n",
       "      <td>Northern</td>\n",
       "      <td>11</td>\n",
       "      <td>0.22</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>George O'Malley</td>\n",
       "      <td>Middle</td>\n",
       "      <td>14</td>\n",
       "      <td>0.33</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Ken Railings</td>\n",
       "      <td>Northern</td>\n",
       "      <td>8</td>\n",
       "      <td>0.35</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Laura Yeager</td>\n",
       "      <td>Southern</td>\n",
       "      <td>1</td>\n",
       "      <td>0.13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Marianne James</td>\n",
       "      <td>Western</td>\n",
       "      <td>6</td>\n",
       "      <td>0.48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Michael Smith</td>\n",
       "      <td>Southern</td>\n",
       "      <td>4</td>\n",
       "      <td>0.15</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Priscilla Taylor</td>\n",
       "      <td>Middle</td>\n",
       "      <td>16</td>\n",
       "      <td>0.51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期               销售经理        地区  销售合同   成交率  Unnamed: 5  \\\n",
       "0   2017-01-01       Aiden Morris   Western    13  0.55         NaN   \n",
       "1   2017-01-01       Audrey Baker   Eastern    11  0.30         NaN   \n",
       "2   2017-01-01  Constantine Eager   Eastern     9  0.36         NaN   \n",
       "3   2017-01-01  Francis Martineau  Northern    11  0.22         NaN   \n",
       "4   2017-01-01    George O'Malley    Middle    14  0.33         NaN   \n",
       "..         ...                ...       ...   ...   ...         ...   \n",
       "155 2020-10-01       Ken Railings  Northern     8  0.35         NaN   \n",
       "156 2020-10-01       Laura Yeager  Southern     1  0.13         NaN   \n",
       "157 2020-10-01     Marianne James   Western     6  0.48         NaN   \n",
       "158 2020-10-01      Michael Smith  Southern     4  0.15         NaN   \n",
       "159 2020-10-01   Priscilla Taylor    Middle    16  0.51         NaN   \n",
       "\n",
       "     备注：本表格中“销售合同”为“已成交合同”。  \n",
       "0                       NaN  \n",
       "1                       NaN  \n",
       "2                       NaN  \n",
       "3                       NaN  \n",
       "4                       NaN  \n",
       "..                      ...  \n",
       "155                     NaN  \n",
       "156                     NaN  \n",
       "157                     NaN  \n",
       "158                     NaN  \n",
       "159                     NaN  \n",
       "\n",
       "[160 rows x 7 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AfricaPerson=pd.read_excel('./data/非洲通讯产品销售数据.xlsx',sheet_name='SalespersonData') #读取数据表格\n",
    "AfricaPerson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>销售经理</th>\n",
       "      <th>地区</th>\n",
       "      <th>销售合同</th>\n",
       "      <th>成交率</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Aiden Morris</td>\n",
       "      <td>Western</td>\n",
       "      <td>13</td>\n",
       "      <td>0.55</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Audrey Baker</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>11</td>\n",
       "      <td>0.30</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Constantine Eager</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>9</td>\n",
       "      <td>0.36</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Francis Martineau</td>\n",
       "      <td>Northern</td>\n",
       "      <td>11</td>\n",
       "      <td>0.22</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>George O'Malley</td>\n",
       "      <td>Middle</td>\n",
       "      <td>14</td>\n",
       "      <td>0.33</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Ken Railings</td>\n",
       "      <td>Northern</td>\n",
       "      <td>8</td>\n",
       "      <td>0.35</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Laura Yeager</td>\n",
       "      <td>Southern</td>\n",
       "      <td>1</td>\n",
       "      <td>0.13</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Marianne James</td>\n",
       "      <td>Western</td>\n",
       "      <td>6</td>\n",
       "      <td>0.48</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Michael Smith</td>\n",
       "      <td>Southern</td>\n",
       "      <td>4</td>\n",
       "      <td>0.15</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Priscilla Taylor</td>\n",
       "      <td>Middle</td>\n",
       "      <td>16</td>\n",
       "      <td>0.51</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期               销售经理        地区  销售合同   成交率  Unnamed: 5\n",
       "0   2017-01-01       Aiden Morris   Western    13  0.55         NaN\n",
       "1   2017-01-01       Audrey Baker   Eastern    11  0.30         NaN\n",
       "2   2017-01-01  Constantine Eager   Eastern     9  0.36         NaN\n",
       "3   2017-01-01  Francis Martineau  Northern    11  0.22         NaN\n",
       "4   2017-01-01    George O'Malley    Middle    14  0.33         NaN\n",
       "..         ...                ...       ...   ...   ...         ...\n",
       "155 2020-10-01       Ken Railings  Northern     8  0.35         NaN\n",
       "156 2020-10-01       Laura Yeager  Southern     1  0.13         NaN\n",
       "157 2020-10-01     Marianne James   Western     6  0.48         NaN\n",
       "158 2020-10-01      Michael Smith  Southern     4  0.15         NaN\n",
       "159 2020-10-01   Priscilla Taylor    Middle    16  0.51         NaN\n",
       "\n",
       "[160 rows x 6 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AfricaPerson.drop('备注：本表格中“销售合同”为“已成交合同”。',axis=1,inplace=True)\n",
    "AfricaPerson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>销售经理</th>\n",
       "      <th>地区</th>\n",
       "      <th>销售合同</th>\n",
       "      <th>成交率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Aiden Morris</td>\n",
       "      <td>Western</td>\n",
       "      <td>13</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Audrey Baker</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>11</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Constantine Eager</td>\n",
       "      <td>Eastern</td>\n",
       "      <td>9</td>\n",
       "      <td>0.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Francis Martineau</td>\n",
       "      <td>Northern</td>\n",
       "      <td>11</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>George O'Malley</td>\n",
       "      <td>Middle</td>\n",
       "      <td>14</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Ken Railings</td>\n",
       "      <td>Northern</td>\n",
       "      <td>8</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Laura Yeager</td>\n",
       "      <td>Southern</td>\n",
       "      <td>1</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Marianne James</td>\n",
       "      <td>Western</td>\n",
       "      <td>6</td>\n",
       "      <td>0.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Michael Smith</td>\n",
       "      <td>Southern</td>\n",
       "      <td>4</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Priscilla Taylor</td>\n",
       "      <td>Middle</td>\n",
       "      <td>16</td>\n",
       "      <td>0.51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期               销售经理        地区  销售合同   成交率\n",
       "0   2017-01-01       Aiden Morris   Western    13  0.55\n",
       "1   2017-01-01       Audrey Baker   Eastern    11  0.30\n",
       "2   2017-01-01  Constantine Eager   Eastern     9  0.36\n",
       "3   2017-01-01  Francis Martineau  Northern    11  0.22\n",
       "4   2017-01-01    George O'Malley    Middle    14  0.33\n",
       "..         ...                ...       ...   ...   ...\n",
       "155 2020-10-01       Ken Railings  Northern     8  0.35\n",
       "156 2020-10-01       Laura Yeager  Southern     1  0.13\n",
       "157 2020-10-01     Marianne James   Western     6  0.48\n",
       "158 2020-10-01      Michael Smith  Southern     4  0.15\n",
       "159 2020-10-01   Priscilla Taylor    Middle    16  0.51\n",
       "\n",
       "[160 rows x 5 columns]"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AfricaPerson.drop(\"Unnamed: 5\",axis=1,inplace=True)\n",
    "AfricaPerson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>销售经理</th>\n",
       "      <th>销售合同</th>\n",
       "      <th>成交率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Aiden Morris</td>\n",
       "      <td>13</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Audrey Baker</td>\n",
       "      <td>11</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Constantine Eager</td>\n",
       "      <td>9</td>\n",
       "      <td>0.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>Francis Martineau</td>\n",
       "      <td>11</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>George O'Malley</td>\n",
       "      <td>14</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Ken Railings</td>\n",
       "      <td>8</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Laura Yeager</td>\n",
       "      <td>1</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Marianne James</td>\n",
       "      <td>6</td>\n",
       "      <td>0.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Michael Smith</td>\n",
       "      <td>4</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>2020-10-01</td>\n",
       "      <td>Priscilla Taylor</td>\n",
       "      <td>16</td>\n",
       "      <td>0.51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            日期               销售经理  销售合同   成交率\n",
       "0   2017-01-01       Aiden Morris    13  0.55\n",
       "1   2017-01-01       Audrey Baker    11  0.30\n",
       "2   2017-01-01  Constantine Eager     9  0.36\n",
       "3   2017-01-01  Francis Martineau    11  0.22\n",
       "4   2017-01-01    George O'Malley    14  0.33\n",
       "..         ...                ...   ...   ...\n",
       "155 2020-10-01       Ken Railings     8  0.35\n",
       "156 2020-10-01       Laura Yeager     1  0.13\n",
       "157 2020-10-01     Marianne James     6  0.48\n",
       "158 2020-10-01      Michael Smith     4  0.15\n",
       "159 2020-10-01   Priscilla Taylor    16  0.51\n",
       "\n",
       "[160 rows x 4 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AfricaPerson.drop(\"地区\",axis=1,inplace=True)\n",
    "AfricaPerson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>销售经理</th>\n",
       "      <th>销售合同</th>\n",
       "      <th>成交率</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aiden Morris</td>\n",
       "      <td>13</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Audrey Baker</td>\n",
       "      <td>11</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Constantine Eager</td>\n",
       "      <td>9</td>\n",
       "      <td>0.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Francis Martineau</td>\n",
       "      <td>11</td>\n",
       "      <td>0.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>George O'Malley</td>\n",
       "      <td>14</td>\n",
       "      <td>0.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>Ken Railings</td>\n",
       "      <td>8</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>Laura Yeager</td>\n",
       "      <td>1</td>\n",
       "      <td>0.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>Marianne James</td>\n",
       "      <td>6</td>\n",
       "      <td>0.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>Michael Smith</td>\n",
       "      <td>4</td>\n",
       "      <td>0.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>Priscilla Taylor</td>\n",
       "      <td>16</td>\n",
       "      <td>0.51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>160 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                  销售经理  销售合同   成交率\n",
       "0         Aiden Morris    13  0.55\n",
       "1         Audrey Baker    11  0.30\n",
       "2    Constantine Eager     9  0.36\n",
       "3    Francis Martineau    11  0.22\n",
       "4      George O'Malley    14  0.33\n",
       "..                 ...   ...   ...\n",
       "155       Ken Railings     8  0.35\n",
       "156       Laura Yeager     1  0.13\n",
       "157     Marianne James     6  0.48\n",
       "158      Michael Smith     4  0.15\n",
       "159   Priscilla Taylor    16  0.51\n",
       "\n",
       "[160 rows x 3 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "AfricaPerson.drop(\"日期\",axis=1,inplace=True)\n",
    "AfricaPerson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>成交合同</th>\n",
       "      <th>成交率</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>销售经理</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Audrey Baker</th>\n",
       "      <td>203</td>\n",
       "      <td>6.050000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Priscilla Taylor</th>\n",
       "      <td>195</td>\n",
       "      <td>6.090000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Aiden Morris</th>\n",
       "      <td>190</td>\n",
       "      <td>7.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  成交合同       成交率\n",
       "销售经理                            \n",
       "Audrey Baker       203  6.050000\n",
       "Priscilla Taylor   195  6.090000\n",
       "Aiden Morris       190  7.666667"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Person=AfricaPerson.groupby(by='销售经理').sum()\n",
    "\n",
    "Person.rename(columns={'销售合同':'成交合同'},inplace=True)\n",
    "Person.to_excel('./../result/result1_3.xlsx')\n",
    "Person.sort_values(by='成交合同',axis=0,ascending=False,inplace=True)\n",
    "Person.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "23e244cc2f3361b11e1cf49f4b7639cf05b01a2bd78e7f3ec527d83f41447468"
  },
  "kernelspec": {
   "display_name": "Python 3.8.8 64-bit ('base': conda)",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
